package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.domain.SysUserDetails;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.P2pWdzrDao;

import net.sf.json.JSONObject;
@Repository
public class P2pWdzrDaoImpl extends P2PJdbcDao implements P2pWdzrDao {
	private static Logger logger=Logger.getLogger(P2pWdzrDaoImpl.class);

	@Override
	public List<Map<String, Object>> queryWdzrSqList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String czrr = params.optString("czrr");
		String cxmid = params.optString("cxmid");
		String czrzt = params.optString("czrzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append("SELECT T1.ID,T1.ZR_LY_TYPE,T1.ZR_REF_XM_ID,T1.ZR_REF_TZRXX_ID,T1.ZR_SQ_USER_ID,T1.XM_MJQ");
		sql.append(",date_format(T1.ZR_SQRQ,'%Y-%m-%d %H:%i:%s') ZR_SQRQ,T1.ZR_XM_NAME,T1.ZR_XM_NH_SYL,T1.ZR_XM_JE,T1.ZR_WD_TZJE,T1.ZR_WD_NAME,T1.ZR_WD_CONTENT,T1.ZR_QX_DAY,T1.ZR_QX_MONTH,");
		sql.append("date_format(T1.ZR_DQRQ,'%Y-%m-%d %H:%i:%s') ZR_DQRQ,T1.ZR_SXF_BL,T1.ZR_SXF,T1.ZR_WD_SJDZJE,T1.WD_ZR_STATE,T1.SH_USER_ID,date_format(T1.SH_RQ,'%Y-%m-%d %H:%i:%s') SH_RQ,T1.SH_CONTENT,T2.HKZT TZ_HKZT");
		sql.append(" FROM t_zhfw_wdzr T1");
		sql.append(" LEFT JOIN t_zhfw_tzrxx T2 ON T1.ZR_REF_TZRXX_ID=T2.ID WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(czrr)){
			sql.append(" AND T1.ZR_SQ_USER_ID like :czrr");
			paramMap.put("czrr","%"+czrr+"%");
		}
		if(StringUtils.isNotEmpty(cxmid)){
			sql.append(" AND T1.ZR_REF_XM_ID =:cxmid");
			paramMap.put("cxmid", cxmid);
		}
		if(StringUtils.isNotEmpty(czrzt)){
			sql.append(" AND T1.WD_ZR_STATE =:czrzt");
			paramMap.put("czrzt", czrzt);
		}
		sql.append(" order by T1.ZR_SQRQ desc LIMIT :START,:LIMIT");
		logger.info("queryWdzrSqList sql is "+sql.toString());
		logger.info("queryWdzrSqList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getWdzrSqCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String czrr = params.optString("czrr");
		String cxmid = params.optString("cxmid");
		String czrzt = params.optString("czrzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append("SELECT COUNT(*) FROM t_zhfw_wdzr T1 WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(czrr)){
			sql.append(" AND T1.ZR_SQ_USER_ID like :czrr");
			paramMap.put("czrr","%"+czrr+"%");
		}
		if(StringUtils.isNotEmpty(cxmid)){
			sql.append(" AND T1.ZR_REF_XM_ID =:cxmid");
			paramMap.put("cxmid", cxmid);
		}
		if(StringUtils.isNotEmpty(czrzt)){
			sql.append(" AND T1.WD_ZR_STATE =:czrzt");
			paramMap.put("czrzt", czrzt);
		}
		logger.info("getWdzrSqCount sql is "+sql.toString());
		logger.info("getWdzrSqCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public int saveWdzrSh(JSONObject params) {
		String wdzrid=params.optString("wdzrid");
		String shr=params.optString("shr");
		String shzt=params.optString("shzt");
		String shbz=params.optString("shbz");
		String mjq=params.optString("mjq");
		String sxfbl=params.optString("sxfbl");
		StringBuffer sql=new StringBuffer();
		sql.append("UPDATE t_zhfw_wdzr SET  WD_ZR_STATE=:shzt,SH_USER_ID=:shr,SH_RQ=now()");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(shbz)) {
			sql.append(",SH_CONTENT=:shbz");
			paramMap.put("shbz", shbz);
		}
		if (StringUtils.isNotEmpty(mjq)) {
			sql.append(",XM_MJQ=:mjq");
			paramMap.put("mjq", mjq);
		}
		if (StringUtils.isNotEmpty(sxfbl)) {
			sql.append(",ZR_SXF_BL=:sxfbl,ZR_SXF=ZR_WD_TZJE*:sxfbl,ZR_WD_SJDZJE=ZR_WD_TZJE-ZR_SXF");
			paramMap.put("sxfbl", Double.valueOf(sxfbl));
		}
		paramMap.put("shr", shr);
		paramMap.put("shzt", shzt);
		paramMap.put("wdzrid", wdzrid);
		sql.append(" where ID=:wdzrid");
		logger.info("saveWdzrSh sql is "+sql.toString());
		logger.info("saveWdzrSh paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryZrTzrxxList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String userid = params.optString("userid");
		String xmid = params.optString("xmid");
		String tzzt = params.optString("tzzt");
		String hkzt = params.optString("hkzt");
		String htbh = params.optString("htbh");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("select t1.ID, t1.USERID, t2.ZR_WD_NAME, t1.TZJE, date_format(t1.TZSJ,'%Y-%m-%d %H:%i:%s') TZSJ, t1.TZZT, t1.HKZT,date_format(t1.JXRQ,'%Y-%m-%d %H:%i:%s') JXRQ,date_format(t1.DQRQ,'%Y-%m-%d %H:%i:%s') DQRQ, t1.NH_SYL, t1.QX_DAY, t1.TZSY, t1.HTBH, t1.XM_LY_TYPE");
		sql.append(" FROM t_zhfw_wdzr_tzrxx t1 left join t_zhfw_wdzr t2 on t1.TZ_WDZR_ID=t2.id WHERE 1=1");
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND t1.USERID like :userid");
			paramMap.put("userid", "%"+userid+"%");
		}
		if(StringUtils.isNotEmpty(xmid)){
			sql.append(" AND t2.ZR_WD_NAME like :xmid");
			paramMap.put("xmid", "%"+xmid+"%");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("00"))){
			sql.append(" and t1.TZZT = '成功' ");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("01"))){
			sql.append(" and t1.TZZT = '待审核' ");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("02"))){
			sql.append(" and t1.TZZT = '失败' ");
		}
		if(StringUtils.isNotEmpty(hkzt)){
			sql.append(" AND t1.HKZT=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND t1.HTBH like :htbh");
			paramMap.put("htbh", "%"+htbh+"%");
		}
		sql.append(" order by ID desc limit :start,:limit ");
		logger.info("queryZrTzrxxList sql is "+sql.toString());
		logger.info("queryZrTzrxxList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getZrTzrxxCount(JSONObject params) {
		String userid = params.optString("userid");
		String xmid = params.optString("xmid");
		String tzzt = params.optString("tzzt");
		String hkzt = params.optString("hkzt");
		String htbh = params.optString("htbh");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) FROM t_zhfw_wdzr_tzrxx t1 left join t_zhfw_wdzr t2 on t1.TZ_WDZR_ID=t2.id WHERE 1=1");
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND t1.USERID like :userid");
			paramMap.put("userid", "%"+userid+"%");
		}
		if(StringUtils.isNotEmpty(xmid)){
			sql.append(" AND t2.ZR_WD_NAME like :xmid");
			paramMap.put("xmid", "%"+xmid+"%");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("00"))){
			sql.append(" and t1.TZZT = '成功' ");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("01"))){
			sql.append(" and t1.TZZT = '待审核' ");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("02"))){
			sql.append(" and t1.TZZT = '失败' ");
		}
		if(StringUtils.isNotEmpty(hkzt)){
			sql.append(" AND t1.HKZT=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND t1.HTBH like :htbh");
			paramMap.put("htbh", "%"+htbh+"%");
		}
		logger.info("getZrTzrxxCount sql is "+sql.toString());
		logger.info("getZrTzrxxCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public float getZrTzrxxSum(JSONObject params) {
		String userid = params.optString("userid");
		String xmid = params.optString("xmid");
		String tzzt = params.optString("tzzt");
		String hkzt = params.optString("hkzt");
		String htbh = params.optString("htbh");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select SUM(TZJE) TZJE FROM t_zhfw_wdzr_tzrxx t1 left join t_zhfw_wdzr t2 on t1.TZ_WDZR_ID=t2.id WHERE 1=1");
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND t1.USERID like :userid");
			paramMap.put("userid", "%"+userid+"%");
		}
		if(StringUtils.isNotEmpty(xmid)){
			sql.append(" AND t2.ZR_WD_NAME like :xmid");
			paramMap.put("xmid", "%"+xmid+"%");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("00"))){
			sql.append(" and t1.TZZT = '成功' ");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("01"))){
			sql.append(" and t1.TZZT = '待审核' ");
		}
		if(StringUtils.isNotEmpty(tzzt)&&(tzzt.equals("02"))){
			sql.append(" and t1.TZZT = '失败' ");
		}
		if(StringUtils.isNotEmpty(hkzt)){
			sql.append(" AND t1.HKZT=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND t1.HTBH like :htbh");
			paramMap.put("htbh", "%"+htbh+"%");
		}
		logger.info("getZrTzrxxSum sql is "+sql.toString());
		logger.info("getZrTzrxxSum paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor() {
			@Override
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				float sJe=0;
				if (rs.next()) {
					sJe=rs.getFloat("TZJE");
				}
				return sJe;
			}
			
		});
	}

	@Override
	public int isValid(JSONObject params) {
		String wdzrid=params.optString("wdzrid");
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT COUNT(*) FROM t_zhfw_wdzr_tz_back where HKZT IN ('1','2')");
		sql.append(" AND REF_TZRXX_ID=(SELECT ZR_REF_TZRXX_ID from t_zhfw_wdzr where ID=:wdzrid AND date_format(ZR_DQRQ,'%Y-%m-%d')>date_format(NOW(),'%Y-%m-%d'))");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("wdzrid", wdzrid);
		logger.info("isValid sql is "+sql.toString());
		logger.info("isValid paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap,Integer.class);
	}

	@Override
	public List<Map<String, Object>> queryWdzrView(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String czrr = params.optString("czrr");
		String cxmid = params.optString("cxmid");
		String czrzt = params.optString("czrzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append("SELECT T1.ID,T1.ZR_LY_TYPE,T1.ZR_REF_XM_ID,T1.ZR_REF_TZRXX_ID,T1.ZR_SQ_USER_ID,");
		sql.append("date_format(T1.ZR_SQRQ,'%Y-%m-%d %H:%i:%s') ZR_SQRQ,T1.ZR_XM_NAME,T1.ZR_XM_NH_SYL,T1.ZR_XM_JE,T1.ZR_WD_TZJE,T1.ZR_WD_NAME,T1.ZR_WD_CONTENT,T1.ZR_QX_DAY,T1.ZR_QX_MONTH,");
		sql.append("date_format(T1.ZR_DQRQ,'%Y-%m-%d %H:%i:%s') ZR_DQRQ,T1.ZR_SXF_BL,T1.ZR_SXF,T1.ZR_WD_SJDZJE,T1.WD_ZR_STATE,T1.SH_USER_ID,date_format(T1.SH_RQ,'%Y-%m-%d %H:%i:%s') SH_RQ,T1.SH_CONTENT");
		sql.append(",T2.TZSJ TZSJ_YT,T2.TCZT TZZT_YT,T2.HKZT HKZT_YT,T2.JXRQ JXRQ_YT,T2.TZSY TZSY_YT,T2.TZ_SOURCE TZ_SOURCE_YT,T2.POS_CODE POS_CODE_YT,T2.HTBH HTBH_YT");
		sql.append(",T3.USERID USERID_XT,date_format(T3.TZSJ,'%Y-%m-%d %H:%i:%s') TZSJ_XT,T3.TZZT TZZT_XT,T3.HKZT HKZT_XT,date_format(T3.JXRQ,'%Y-%m-%d %H:%i:%s') JXRQ_XT,T3.QX_DAY QX_DAY_XT,T3.TZSY TZSY_XT,T3.HTBH HTBH_XT");
		sql.append(" FROM t_zhfw_wdzr T1");
		sql.append(" LEFT JOIN t_zhfw_tzrxx T2 ON T1.ZR_REF_TZRXX_ID=T2.ID");
		sql.append(" LEFT JOIN t_zhfw_wdzr_tzrxx T3 ON T1.ID=T3.TZ_WDZR_ID WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(czrr)){
			sql.append(" AND T1.ZR_SQ_USER_ID like :czrr");
			paramMap.put("czrr","%"+czrr+"%");
		}
		if(StringUtils.isNotEmpty(cxmid)){
			sql.append(" AND T1.ZR_REF_XM_ID =:cxmid");
			paramMap.put("cxmid", cxmid);
		}
		if(StringUtils.isNotEmpty(czrzt)){
			sql.append(" AND T1.WD_ZR_STATE =:czrzt");
			paramMap.put("czrzt", czrzt);
		}
		sql.append(" order by T1.ZR_SQRQ desc LIMIT :START,:LIMIT");
		logger.info("queryWdzrView sql is "+sql.toString());
		logger.info("queryWdzrView paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int queryWdzrCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String czrr = params.optString("czrr");
		String cxmid = params.optString("cxmid");
		String czrzt = params.optString("czrzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append("SELECT count(*) FROM t_zhfw_wdzr T1 WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(T1.ZR_SQRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(czrr)){
			sql.append(" AND T1.ZR_SQ_USER_ID like :czrr");
			paramMap.put("czrr","%"+czrr+"%");
		}
		if(StringUtils.isNotEmpty(cxmid)){
			sql.append(" AND T1.ZR_REF_XM_ID =:cxmid");
			paramMap.put("cxmid", cxmid);
		}
		if(StringUtils.isNotEmpty(czrzt)){
			sql.append(" AND T1.WD_ZR_STATE =:czrzt");
			paramMap.put("czrzt", czrzt);
		}
		logger.info("queryWdzrCount sql is "+sql.toString());
		logger.info("queryWdzrCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public int updateTzrxx(JSONObject params) {
		String id=params.optString("tzrxxid");
		String zrzt=params.optString("zrzt");
		String sql="UPDATE t_zhfw_tzrxx SET ZR_STATUS=:zrzt WHERE ID=:id";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("zrzt", zrzt);
		paramMap.put("id", id);
		logger.info("updateTzrxx sql is "+sql);
		logger.info("updateTzrxx paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updateWdzrTzrxx(JSONObject params) {
		String id=params.optString("tzrxxid");
		String zrzt=params.optString("zrzt");
		String tzzt=params.optString("tzzt");
		String sql="UPDATE t_zhfw_wdzr_tz_back SET ZR_STATUS=:zrzt,TZZT=:tzzt WHERE REF_TZRXX_ID=:id order by BACK_DATE desc limit 1";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("zrzt", zrzt);
		paramMap.put("tzzt", tzzt);
		paramMap.put("id", id);
		logger.info("updateWdzrTzrxx sql is "+sql);
		logger.info("updateWdzrTzrxx paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updateJhnTzrxx(JSONObject params) {
		String id=params.optString("tzrxxid");
		String zrzt=params.optString("zrzt");
		String sql="UPDATE t_zhfw_jhn_tzrxx SET ZR_STATUS=:zrzt WHERE ID=:id";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("zrzt", zrzt);
		paramMap.put("id", id);
		logger.info("updateJhnTzrxx sql is "+sql);
		logger.info("updateJhnTzrxx paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> queryWdzrQxList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String yhm = params.optString("yhm");
		String qxzt = params.optString("qxzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select ID,USER_NAME,case when (QX_STATUS='1' and SQ_START_DATE<=now() and GQ_END_DATE>=now()) then '1' else '0' end QX_STATUS,date_format(SQ_START_DATE,'%Y-%m-%d %H:%i:%s') SQ_START_DATE");
		sql.append( ",date_format(GQ_END_DATE,'%Y-%m-%d %H:%i:%s') GQ_END_DATE,OPER_USER_ID from t_zhfw_wdzr_qx ");
		sql.append("where 1=1");
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and USER_NAME=:yhm");
			paramMap.put("yhm", yhm);
		}
		if (StringUtils.isNotEmpty(qxzt)&&StringUtils.equals("0", qxzt)) {//当前时间无权限
			sql.append(" and (QX_STATUS='0' or QX_STATUS='2' or QX_STATUS is null or SQ_START_DATE>now() or GQ_END_DATE<now())");
		}else if (StringUtils.isNotEmpty(qxzt)&&StringUtils.equals("1", qxzt)){
			sql.append(" and QX_STATUS='1' and SQ_START_DATE<=now() and GQ_END_DATE>=now()");
		}
		sql.append(" order by SQ_START_DATE desc,GQ_END_DATE desc ");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryWdzrQxList sql is "+sql.toString());
		logger.info("queryWdzrQxList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getWdzrQxCount(JSONObject params) {
		String yhm = params.optString("yhm");
		String qxzt = params.optString("qxzt");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) from t_zhfw_wdzr_qx ");
		sql.append("where 1=1");
		if (StringUtils.isNotEmpty(yhm)) {
			sql.append(" and USER_NAME=:yhm");
			paramMap.put("yhm", yhm);
		}
		if (StringUtils.isNotEmpty(qxzt)&&StringUtils.equals("0", qxzt)) {//当前时间无权限
			sql.append(" and (QX_STATUS='0'  or QX_STATUS='2' or QX_STATUS is null or SQ_START_DATE>now() or GQ_END_DATE<now())");
		}else if (StringUtils.isNotEmpty(qxzt)&&StringUtils.equals("1", qxzt)){
			sql.append(" and QX_STATUS='1' and SQ_START_DATE<=now() and GQ_END_DATE>=now()");
		}
		logger.info("getWdzrQxCount sql is "+sql.toString());
		logger.info("getWdzrQxCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public JSONObject saveWdzrQx(JSONObject params) {
		SysUserDetails sysUserDetails = (SysUserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		JSONObject result=new JSONObject();
		String yhm=params.optString("yhm");
		String sqsx=params.optString("sqsx");
		String sqr=params.optString("sqr");
		result.put("success", true);
		if (null!=sysUserDetails&&!StringUtils.equals(sqr, sysUserDetails.getGyh())) {
			result.put("isSuc", false);
			result.put("msg", "不是当前登录用户操作,请重新登录");
			return result;
		}
		Map<String, Object> paramMap=new HashMap<>();
		String sql_user="select count(*) from t_zhfw_user where disabled ='1' and user_name = '"+yhm+"'";
		if (this.getNamedParameterJdbcTemplate().queryForInt(sql_user, paramMap)<=0) {
			result.put("isSuc", false);
			result.put("msg", "用户名不存在,请核实后再授权");
			return result;
		}
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_wdzr_qx(ID, USER_NAME, QX_STATUS, SQ_START_DATE, GQ_END_DATE, OPER_USER_ID) ");
		sql.append(" values(uuid(),:yhm,'1',now()");
		paramMap.put("yhm", yhm);
		paramMap.put("sqr", sqr);
		if (StringUtils.equals("01", sqsx)) {
			sql.append(",date_add(now(),interval 7 day)");
		}else if (StringUtils.equals("02", sqsx)) {
			sql.append(",date_add(now(),interval 14 day)");
		}else if (StringUtils.equals("03", sqsx)) {
			sql.append(",date_add(now(),interval 1 month)");
		}else if (StringUtils.equals("04", sqsx)) {
			sql.append(",date_add(now(),interval 3 month)");
		}else {
			sql.append(",now()");
		}
		sql.append(",:sqr)");
		int isSuc=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (isSuc>0) {
			result.put("isSuc", true);
			result.put("msg", "授权成功");
		}else {
			result.put("isSuc", false);
			result.put("msg", "授权失败");
		}
		logger.info("saveWdzrQx sql is "+sql.toString());
		logger.info("saveWdzrQx paramMap is "+paramMap);
		return result;
	}

	@Override
	public boolean deleteWdzrQx(String username) {
		String sql="update t_zhfw_wdzr_qx set QX_STATUS='2' where USER_NAME='"+username+"'";
		Map<String, Object> paramMap=new HashMap<>();
		logger.info("deleteWdzrQx sql is "+sql);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap)>0;
	}

}
